# RUN02_Finance_Indicator_Calculate_s_Stocks_Daily_s_SQLITE3.py
# Create by GF 2025-08-12 12:55

import sqlite3
import sys
import traceback
# ..................................................
sys.path.append("./GF_PY3_CLASS_Python_3_Text")
sys.path.append("./GF_PY3_CLASS_Finance")
# ..................................................
import pandas      # Pandas 2.0.3
import sqlalchemy  # SQLAlchemy 2.0.36
# ..................................................
from GF_PY3_CLASS.Python_3_Text_Progress_Bar import Python_3_Text_Progress_Bar
# ..................................................
from GF_PY3_CLASS_Finance_Indicator_SMA_s_SQLITE3 import Finance_Indicator_SMA_s_SQLITE3
from GF_PY3_CLASS_Finance_Indicator_EMA_s_SQLITE3 import Finance_Indicator_EMA_s_SQLITE3
from GF_PY3_CLASS_Finance_Indicator_MACD_s_SQLITE3 import Finance_Indicator_MACD_s_SQLITE3
from GF_PY3_CLASS_Finance_Indicator_KDJ_s_SQLITE3 import Finance_Indicator_KDJ_s_SQLITE3
from GF_PY3_CLASS_Finance_Entanglement_Theory_s_SQLITE3 import Finance_Entanglement_Theory_s_SQLITE3
from GF_PY3_CLASS_Finance_Data_Conv_Daily_to_Weekly_s_SQLITE3 import Finance_Data_Conv_Daily_to_Weekly_s_SQLITE3
# ..................................................
from GF_PY3_CLASS_Finance_Indicator_SMA_s_SQLITE3_s_BR import Finance_Indicator_SMA_s_SQLITE3_s_BR
from GF_PY3_CLASS_Finance_Indicator_EMA_s_SQLITE3_s_BR import Finance_Indicator_EMA_s_SQLITE3_s_BR
from GF_PY3_CLASS_Finance_Indicator_MACD_s_SQLITE3_s_BR import Finance_Indicator_MACD_s_SQLITE3_s_BR

# ##################################################

SQLITE3_PATH                                     = r"./GF_SQLITE3_FINANCE.db"
DB_TABLE_DAILY_DATA                              = r"tushare_api_cache_en_us"
DB_TABLE_DAILY_DATA_INDICATOR                    = f"tushare_api_cache_en_us_indicator"
DB_TABLE_WEEKLY_DATA_DAY_BY_DAY_UPDATE           = r"stocks_en_us_weekly_data_day_by_day_update"
STOCKS_ADJUSTED                                  = r"不复权"
SQL_STATMENT_STOCKS_DAILY_ALL                    = f"SELECT * FROM {DB_TABLE_DAILY_DATA} WHERE memo REGEXP '.*日数据.*|.*{STOCKS_ADJUSTED}.*';"
SQL_STATMENT_STOCKS_DAILY_ONLY_WITHOUT_INDICATOR = f"SELECT * FROM {DB_TABLE_DAILY_DATA} WHERE memo REGEXP '.*日数据.*|.*{STOCKS_ADJUSTED}.*' AND id NOT IN (SELECT id FROM {DB_TABLE_DAILY_DATA_INDICATOR});"

# ##################################################

def DIY_Finance_DataFrame_Preparing_for_Daily(DataFrame):

    df = DataFrame.copy()

    # 创建 "映射表 - 列名"
    Mapping_Table_for_Column_Name = {"ts_code": "code", "pct_chg": "chg_pct", "vol": "volume"}

    # 使用 "映射表 - 列名"
    df = df.rename(columns = Mapping_Table_for_Column_Name)

    # ############ #
    # 数据类型调整 #
    # ############ #

    df["time0"  ] = pandas.to_datetime(df["time"], format = "mixed", errors = "coerce")  # errors = "coerce" 将无效日期转为 NaT
    # ..............................................
    df["open"   ] = df["open"   ].astype("float64")
    df["high"   ] = df["high"   ].astype("float64")
    df["low"    ] = df["low"    ].astype("float64")
    df["close"  ] = df["close"  ].astype("float64")
    df["change" ] = df["change" ].astype("float64")
    df["volume" ] = df["volume" ].astype("float64")

    # ###################### #
    # 数据准备: 指标逐日计算 #
    # ###################### #

    df["row_num"] = df.groupby("code")["time0"].rank(method = "first", ascending = True)
    df["row_num"] = df["row_num"].astype("int64")

    # ######################## #
    # 数据准备: 周数据逐日更新 #
    # ######################## #

    # 提取必要数据: 年份 (Year), 本年中第几周 (Week Number)
    df["year"    ] = df["time0"   ].dt.year
    df["year"    ] = df["year"    ].astype("string")
    df["week_num"] = df["time0"   ].dt.strftime("%U")  # 其中 %U 表示本年中的第几周
    df["week_num"] = df["week_num"].str.zfill(2)       # 函数 str().zfill() 用于将字符串填充 0 至指定宽度
    # ..............................................
    df["iso_8601_week"] = df["year"] + "-W" + df["week_num"]  # ISO 8601 表示周采用 YYYY-Www 格式
    # ..............................................
    df["row_num_wddbdu"] = df[["code", "iso_8601_week", "time0"]].groupby(["code", "iso_8601_week"], as_index = False).rank(method = "first", ascending = True)
    df["row_num_wddbdu"] = df["row_num_wddbdu"].astype("int64")

    # ############ #
    # 数据准备就绪 #
    # ############ #

    df = df.sort_values(["code", "time0"], ascending = [True, True])
    df = df.reset_index(drop = True)

    return df

def DIY_Finance_DataFrame_Indicator_Calculate_s_Stocks_Daily_s_SQLITE3(Finance_DataFrame, Weekly_Data_Day_by_Day_Update:bool):

    df = DIY_Finance_DataFrame_Preparing_for_Daily(Finance_DataFrame)

    TextProgressBar = Python_3_Text_Progress_Bar()

    # 创建 SQLite 3 数据库连接
    SQLITE3_CONNECT = sqlite3.connect(SQLITE3_PATH, check_same_thread = False, timeout = 30)  # check_same_thread = False 允许在不同的线程中使用同一个连接。

    Obj101 = Finance_Indicator_SMA_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj102 = Finance_Indicator_SMA_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj201 = Finance_Indicator_EMA_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj202 = Finance_Indicator_EMA_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj300 = Finance_Indicator_MACD_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj400 = Finance_Indicator_KDJ_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj500 = Finance_Entanglement_Theory_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj600 = Finance_Data_Conv_Daily_to_Weekly_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_WEEKLY_DATA_DAY_BY_DAY_UPDATE)

    Total = df["id"].count()
    Count = 1
    for Idx, Row in df.iterrows():
        ID             = Row["id"]
        Code           = Row["code"]
        ROW_NUM        = Row["row_num"]
        ROW_NUM_WDDBDU = Row["row_num_wddbdu"]
        Time           = Row["time"]
        Open           = Row["open"]
        High           = Row["high"]
        Low            = Row["low"]
        Close          = Row["close"]
        Change         = Row["change"]
        Volume         = Row["volume"]
        # ..........................................
        sys.stdout.write(f"""\r[DEBUG] PROCESSING: {TextProgressBar.Double_Line_Arrow(Count = Count, Total = Total)}""")
        sys.stdout.flush()
        # ..........................................
        SMA5  = Obj101.UPDATE_OR_INSERT_SMA(ID = ID, ROW_NUM = ROW_NUM, Period = 5, Close = Close)
        SMA10 = Obj102.UPDATE_OR_INSERT_SMA(ID = ID, ROW_NUM = ROW_NUM, Period = 10, Close = Close)
        # ..........................................
        EMA12 = Obj201.UPDATE_OR_INSERT_EMA(ID = ID, ROW_NUM = ROW_NUM, Period = 12, Close = Close)
        EMA26 = Obj202.UPDATE_OR_INSERT_EMA(ID = ID, ROW_NUM = ROW_NUM, Period = 26, Close = Close)
        # ..........................................
        MACD_DIF   = Obj300.UPDATE_OR_INSERT_MACD_DIF(ID = ID, EMA12 = EMA12["value"], EMA26 = EMA26["value"])
        MACD_DEA   = Obj300.UPDATE_OR_INSERT_MACD_DEA(ID = ID, ROW_NUM = ROW_NUM, MACD_DIF = MACD_DIF["value"])
        MACD_STICK = Obj300.UPDATE_OR_INSERT_MACD_STICK(ID = ID, MACD_DIF = MACD_DIF["value"], MACD_DEA = MACD_DEA["value"])
        # ..........................................
        KDJ_K = Obj400.UPDATE_OR_INSERT_KDJ_K(ID = ID, ROW_NUM = ROW_NUM, RSV_Prd = 9, K_Prd = 3, High = High, Low = Low, Close = Close)
        KDJ_D = Obj400.UPDATE_OR_INSERT_KDJ_D(ID = ID, ROW_NUM = ROW_NUM, RSV_Prd = 9, D_Prd = 3, K_Val = KDJ_K["value"])
        KDJ_J = Obj400.UPDATE_OR_INSERT_KDJ_J(ID = ID, K_Val = KDJ_K["value"], D_Val = KDJ_D["value"])
        # ..........................................
        ETG_TRS     = Obj500.UPDATE_OR_INSERT_ETG_Top_Reversal_Shape(ID = ID, ROW_NUM = ROW_NUM, Input_UpperEdge = High, Input_LowerEdge = Low)
        ETG_BRS     = Obj500.UPDATE_OR_INSERT_ETG_Bottom_Reversal_Shape(ID = ID, ROW_NUM = ROW_NUM, Input_UpperEdge = High, Input_LowerEdge = Low)
        ETG_T_Group = Obj500.UPDATE_OR_INSERT_ETG_Top_Reversal_Shape_s_Group_Top(ID = ID, ROW_NUM = ROW_NUM, Input_UpperEdge = High, Input_LowerEdge = Low)
        ETG_B_Group = Obj500.UPDATE_OR_INSERT_ETG_Bottom_Reversal_Shape_s_Group_Bottom(ID = ID, ROW_NUM = ROW_NUM, Input_UpperEdge = High, Input_LowerEdge = Low)
        # ..........................................
        if (Weekly_Data_Day_by_Day_Update == True):
            TEXT_Value_01 = Obj600.UPDATE_OR_INSERT_TEXT_Field_Value_by_ID(ID = ID, Field = "code", TEXT_Value = Code)  # SQLITE3 TEXT 值
            WeeK_Number   = Obj600.UPDATE_OR_INSERT_Week_Number(ID = ID, Time = Time)
            WK_Time       = Obj600.UPDATE_OR_INSERT_DY_to_WK_Time(ID = ID, ROW_NUM = ROW_NUM_WDDBDU, DY_Time = Time)
            WK_Open       = Obj600.UPDATE_OR_INSERT_DY_to_WK_Open(ID = ID, ROW_NUM = ROW_NUM_WDDBDU, DY_Open = Open)
            WK_High       = Obj600.UPDATE_OR_INSERT_DY_to_WK_High(ID = ID, ROW_NUM = ROW_NUM_WDDBDU, DY_High = High)
            WK_Low        = Obj600.UPDATE_OR_INSERT_DY_to_WK_Low(ID = ID, ROW_NUM = ROW_NUM_WDDBDU, DY_Low = Low)
            WK_Close      = Obj600.UPDATE_OR_INSERT_DY_to_WK_Close(ID = ID, ROW_NUM = ROW_NUM_WDDBDU, DY_Close = Close)
            WK_Change     = Obj600.UPDATE_OR_INSERT_DY_to_WK_Change(ID = ID, ROW_NUM = ROW_NUM_WDDBDU, DY_Change = Change)
            WK_Volume     = Obj600.UPDATE_OR_INSERT_DY_to_WK_Volume(ID = ID, ROW_NUM = ROW_NUM_WDDBDU, DY_Volume = Volume)
        # ..........................................
        Count = Count + 1

    sys.stdout.write(f"""\n""")

    # 提交 SQLite 3 数据库事务 / 关闭 SQLite 3 数据库连接
    SQLITE3_CONNECT.commit()
    SQLITE3_CONNECT.close()

def DIY_Finance_DataFrame_Indicator_Calculate_s_Stocks_Daily_s_SQLITE3_s_BR(Finance_DataFrame, Weekly_Data_Day_by_Day_Update:bool):

    df = DIY_Finance_DataFrame_Preparing_for_Daily(Finance_DataFrame)
    df["row_num"    ] = df["row_num"] + 1
    df["max_row_num"] = df["row_num"].max()

    TextProgressBar = Python_3_Text_Progress_Bar()

    # 创建 SQLite 3 数据库连接
    SQLITE3_CONNECT = sqlite3.connect(SQLITE3_PATH, check_same_thread = False, timeout = 30)

    Obj101 = Finance_Indicator_SMA_s_SQLITE3_s_BR(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj102 = Finance_Indicator_SMA_s_SQLITE3_s_BR(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj201 = Finance_Indicator_EMA_s_SQLITE3_s_BR(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj202 = Finance_Indicator_EMA_s_SQLITE3_s_BR(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj301 = Finance_Indicator_MACD_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)
    Obj302 = Finance_Indicator_MACD_s_SQLITE3_s_BR(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_DAILY_DATA_INDICATOR)

    Total = df["id"].count()
    Count = 1
    for Idx, Row in df.iterrows():
        ID          = Row["id"]
        Code        = Row["code"]
        ROW_NUM     = Row["row_num"]
        MAX_ROW_NUM = Row["max_row_num"]
        Time        = Row["time"]
        Open        = Row["open"]
        High        = Row["high"]
        Low         = Row["low"]
        Close       = Row["close"]
        Change      = Row["change"]
        Volume      = Row["volume"]
        # ..........................................
        sys.stdout.write(f"""\r[DEBUG] PROCESSING: {TextProgressBar.Double_Line_Arrow(Count = Count, Total = Total)}""")
        sys.stdout.flush()
        # ..........................................
        SMA5  = Obj101.UPDATE_OR_INSERT_SMA_s_BR(Table = DB_TABLE_DAILY_DATA_INDICATOR, ID = ID, Time = Time, Code = Code, ROW_NUM = ROW_NUM, MAX_ROW_NUM = MAX_ROW_NUM, Period = 5,  Close = Close)
        SMA10 = Obj102.UPDATE_OR_INSERT_SMA_s_BR(Table = DB_TABLE_DAILY_DATA_INDICATOR, ID = ID, Time = Time, Code = Code, ROW_NUM = ROW_NUM, MAX_ROW_NUM = MAX_ROW_NUM, Period = 10, Close = Close)
        # ..........................................
        EMA12 = Obj201.UPDATE_OR_INSERT_EMA_s_BR(Table = DB_TABLE_DAILY_DATA_INDICATOR, ID = ID, Time = Time, Code = Code, ROW_NUM = ROW_NUM, MAX_ROW_NUM = MAX_ROW_NUM, Period = 12, Close = Close)
        EMA26 = Obj202.UPDATE_OR_INSERT_EMA_s_BR(Table = DB_TABLE_DAILY_DATA_INDICATOR, ID = ID, Time = Time, Code = Code, ROW_NUM = ROW_NUM, MAX_ROW_NUM = MAX_ROW_NUM, Period = 26, Close = Close)
        # ..........................................
        MACD_DIF   = Obj301.UPDATE_OR_INSERT_MACD_DIF(ID = ID, EMA12 = EMA12["value"], EMA26 = EMA26["value"])
        MACD_DEA   = Obj302.UPDATE_OR_INSERT_MACD_DEA_s_BR(Table = DB_TABLE_DAILY_DATA_INDICATOR, ID = ID, Time = Time, Code = Code, ROW_NUM = ROW_NUM, MAX_ROW_NUM = MAX_ROW_NUM, MACD_DIF = MACD_DIF["value"])
        MACD_STICK = Obj301.UPDATE_OR_INSERT_MACD_STICK(ID = ID, MACD_DIF = MACD_DIF["value"], MACD_DEA = MACD_DEA["value"])
        # ..........................................
        Count = Count + 1

    sys.stdout.write(f"""\n""")

    # 提交 SQLite 3 数据库事务 / 关闭 SQLite 3 数据库连接
    SQLITE3_CONNECT.commit()
    SQLITE3_CONNECT.close()

# ##################################################

try:

    print(f"""[DEBUG] Finance Indicator Calculate s Stocks Daily s SQLITE3 20250812 版""")
    print(f"""==================================================""")
    print(f"""[DEBUG] 输入 <任意数字> 并按 <Enter> 开始任务""")
    Inputed = input()

    print(f"""[DEBUG] [1] 金融指标计算 - 全量计算 (单线程)""")
    print(f"""[DEBUG] [2] 金融指标计算 - 全量计算 (多线程)""")
    print(f"""[DEBUG] [3] 金融指标计算 - 增量计算 (单线程)""")
    print(f"""==================================================""")
    print(f"""[DEBUG] 输入 <数字> 选择 <任务> 开始执行""")
    print(f"""[DEBUG] 输入 <数字>:""")
    Inputed = input()

    if (Inputed == '1'):

        # SQLite 3 in SQLalchemy URI be Similar to: "sqlite:///C:\\EXAMPLE.db"
        engine = sqlalchemy.create_engine(f"sqlite:///{SQLITE3_PATH}")
        df = pandas.read_sql_query(SQL_STATMENT_STOCKS_DAILY_ALL, con = engine, dtype = str)
        df = df.rename(columns = {"ts_code": "code", "pct_chg": "chg_pct", "vol": "volume"})

        DIY_Finance_DataFrame_Indicator_Calculate_s_Stocks_Daily_s_SQLITE3(Finance_DataFrame = df, Weekly_Data_Day_by_Day_Update = True)

    if (Inputed == '2'):

        engine = sqlalchemy.create_engine(f"sqlite:///{SQLITE3_PATH}")
        df = pandas.read_sql_query(SQL_STATMENT_STOCKS_DAILY_ALL, con = engine, dtype = str)
        df = df.rename(columns = {"ts_code": "code", "pct_chg": "chg_pct", "vol": "volume"})

    if (Inputed == '3'):

        engine = sqlalchemy.create_engine(f"sqlite:///{SQLITE3_PATH}")
        # ..........................................
        result = []
        # ..........................................
        with engine.connect() as connected:
            queried = connected.execute(sqlalchemy.text("""
                SELECT    t0.time, t0.ts_code, t1.ema12
                FROM      tushare_api_cache_en_us                                   AS t0
                LEFT JOIN (SELECT id, ema12 FROM tushare_api_cache_en_us_indicator) AS t1 ON t0.id = t1.id
                WHERE     DATETIME(t0.time) in (SELECT MAX(DATETIME(time)) FROM tushare_api_cache_en_us) AND
                          t0.ts_code = '000001.SZ';
            """))
            # ......................................
            for row in queried:
                result.append(row)
        # ..........................................
        df = pandas.read_sql_query(SQL_STATMENT_STOCKS_DAILY_ONLY_WITHOUT_INDICATOR, con = engine, dtype = str)
        df = df.rename(columns = {"ts_code": "code", "pct_chg": "chg_pct", "vol": "volume"})

        DIY_Finance_DataFrame_Indicator_Calculate_s_Stocks_Daily_s_SQLITE3_s_BR(Finance_DataFrame = df, Weekly_Data_Day_by_Day_Update = True)

    print(f"""[DEBUG] 数据库: SQLITE3_DATASET.db 已更新""")
    print(f"""==================================================""")
    print(f"""[DEBUG] 输入 <任意数字> 并按 <Enter> 退出程序""")
    Inputed = input()

except Exception as e:

    print(f"""[DEBUG] 发生异常""")
    print(f"""==================================================""")
    traceback.print_exc()  # 打印异常信息到控制台, 而不是引发异常后退出
    print(f"""==================================================""")
    print(f"""[DEBUG] 输入 <任意数字> 并按 <Enter> 退出程序""")
    Inputed = input()

# Signed by GF.
